---
title: "pk80001_legalnet stored procedure documentation"
summary: "Retrieved Oracle package used by SAIF.Application.Claim.Legal for letter-request inserts, case-header retrieval, and attorney-manager email lookup."
status: draft
owner: "SAIF Documentation Pipeline"
last_reviewed: "2026-08-25"
source_schema: "WCIS_DBA"
source_name: "pk80001_legalnet"
source_type: "PACKAGE"
library: SAIF.Application.Claim.Legal
reference_type: embedded
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk80001_legalnet"
retrieval_date: "2026-08-25"
---
# pk80001_legalnet

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Application.Claim.Legal` |
| **Calling classes** | `LegalManagerBase`, `clsLegalData`, `clsScheduleManager` |

The retrieved Oracle source declares `PACKAGE Pk80001_Legalnet`. This library references the same package as both `pk80001_LEGALNET` and `pk80001_legalNet`; Oracle identifier casing does not change package resolution.

## Purpose

`Pk80001_Legalnet` centralizes LegalNet database behavior for legal-case retrieval and maintenance, including schedules, appeals, case notes, letter requests, and attorney/staff lookups. For this library, the relevant entry points create `LETTER_REQUEST` rows, resolve the manager email for the attorney attached to a case's most recent hearing, and expose the case-header values used to populate the application's current legal-case context*.

## Procedures / Methods

### insertLetterRequest

| Field | Value |
| --- | --- |
| **Returns** | `requestNum IN OUT NUMBER` |
| **Called by** | `LegalManagerBase.insertLetterRequest` |

**Description:** Allocates `LETTER_REQUEST_SEQ.NEXTVAL`, inserts a `LETTER_REQUEST` row, and stamps `CREATE_DATE = SYSDATE`. The retrieved body accepts `aClaimId` but does not reference it during the insert.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `letterTypeCode` | IN | `VARCHAR2` | — | Letter type to queue, such as the DEF or recon-request letter code. |
| `aLegalCaseNum` | IN | `NUMBER` | — | Legal case sequence number written to `LETTER_REQUEST.LEGAL_CASE_SEQ_NUM`. |
| `holdDate` | IN | `DATE` | — | Hold or mail date stored on the request row. |
| `aClaimId` | IN | `NUMBER` | — | Claim identifier accepted by the signature but unused in the retrieved body. |
| `createUser` | IN | `VARCHAR2` | — | User ID written to `LETTER_REQUEST.CREATE_USER`. |
| `requestNum` | IN OUT | `NUMBER` | — | Output sequence populated from `LETTER_REQUEST_SEQ.NEXTVAL`. |

### getHeader inferred mapping

| Field | Value |
| --- | --- |
| **Returns** | `REF CURSOR` header row contract observed in `clsLegalData`* |
| **Called by** | `clsLegalData.New` |

**Description:** This library calls `pk80001_legalNet.getHeader`, but the retrieved `WCIS_DBA.Pk80001_Legalnet` source does not contain an exact `getHeader` declaration. The closest retrieved header-producing routines are `getLegalCase`, `get_case_num`, `get_asgn_atty`, and `get_hearing_date`, which collectively expose the same case-number, attorney, and hearing-date concepts that `clsLegalData` reads*.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | `NUMBER` | — | Legal case sequence number supplied by `clsLegalData`. |
| `recordset` | OUT | `REF CURSOR` | — | Cursor contract expected by `clsLegalData` for header values* |

#### Relevant retrieved routines

| Routine | Type | Relevant behavior |
| --- | --- | --- |
| `getLegalCase` | Procedure | Returns the core `LEGAL_CASE` row and computes `CASE_NUM` with `get_case_num`. |
| `get_case_num` | Function | Chooses the first populated identifier in WCB → DCBS → OWCP → OALJ → BRB order. |
| `get_asgn_atty` | Function | Resolves attorney display text from the latest appeal attorney, then the most recent hearing attorney, then the outside counsel firm. |
| `get_hearing_date` | Function | Returns the latest hearing date, including consolidated-case hearing lookup logic. |

### get_atty_mngr_email

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` |
| **Called by** | `clsScheduleManager.getAttyMngrEmail` |

**Description:** Resolves the manager email for the attorney tied to the case's most recent hearing. If the assigned-attorney lookup fails, the function falls back to the `OUTSIDECNCL` attorney profile when an outside-firm assignment exists; if neither path resolves, it returns `NONE`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | `NUMBER` | — | Legal case sequence number used to locate the most recent hearing and assigned attorney context. |

## Business Rules

Rules extracted from this package's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [BR-LGL-018](../business-rules.md#br-lgl-018-letter-requests-use-a-database-sequence-and-server-timestamp) | Letter requests use a database sequence and Oracle server timestamp. | `insertLetterRequest` is invoked from the DEF or recon-letter flows. | The package assigns `LETTER_REQUEST_SEQ.NEXTVAL`, inserts the request row, and sets `CREATE_DATE = SYSDATE`. |
| [BR-LGL-019](../business-rules.md#br-lgl-019-manager-email-lookup-falls-back-from-assigned-attorney-to-outside-counsel) | Manager-email lookup falls back from assigned attorney to outside counsel. | `get_atty_mngr_email` cannot find a manager email for the most recent hearing's assigned attorney. | The package retries against the `OUTSIDECNCL` profile when `ASGN_OTHER_FIRM_SEQ_NUM` is present, then returns `NONE` if no email is found. |
| [BR-LGL-020](../business-rules.md#br-lgl-020-header-case-number-prefers-wcb-then-dcbs-owcp-oalj-and-brb) | Header case number prefers WCB, then DCBS, OWCP, OALJ, and BRB* | This library's `getHeader` call is matched to the retrieved `get_case_num` helper* | The first populated identifier in that precedence order is returned for display* |
| [BR-LGL-021](../business-rules.md#br-lgl-021-header-attorney-display-prefers-appeal-attorney-then-hearing-attorney-then-outside-counsel) | Header attorney display prefers appeal attorney, then hearing attorney, then outside counsel* | This library's `getHeader` call is matched to the retrieved `get_asgn_atty` helper* | The first available attorney or firm display value in that order is returned* |
| [BR-LGL-022](../business-rules.md#br-lgl-022-header-hearing-date-uses-the-latest-hearing-and-consolidation-context) | Header hearing date uses the latest hearing and consolidation context* | This library's `getHeader` call is matched to the retrieved `get_hearing_date` helper* | The latest applicable hearing date is returned for the case or its consolidated primary-case context* |

## Operations

Operations and processes implemented in this package for this library:

| Operation | Description | Trigger |
| --- | --- | --- |
| Record letter request | Inserts a `LETTER_REQUEST` row and returns the generated request sequence. | `LegalManagerBase.insertLetterRequest` runs from DEF or recon-request flows. |
| Resolve attorney manager email | Looks up the assigned attorney manager email for the most recent hearing, with outside-counsel fallback. | `clsScheduleManager.SendDEFEmail` calls `getAttyMngrEmail`. |
| Assemble legal case header* | Supplies case-number, attorney, and hearing-date header values through retrieved header helper routines* | `clsLegalData.New` calls `pk80001_legalNet.getHeader`* |

## Source Code

<details>
<summary>Package specification excerpt</summary>

```sql
PACKAGE                   Pk80001_Legalnet AS

TYPE ref_cursor IS REF CURSOR;

FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE;

FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN VARCHAR2;

FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

FUNCTION get_case_num(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor);

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER);
END;
```

</details>

<details>
<summary>insertLetterRequest body</summary>

```sql
PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER)
  AS
   v_send     VARCHAR2(3);
   BEGIN

   v_send := 'YES';

  SELECT letter_request_seq.NEXTVAL
    INTO requestNum
    FROM dual;

  INSERT INTO LETTER_REQUEST
   (letter_request_Seq_num,
    LETTER_TYPE_CODE,
    legal_case_seq_num,
    hold_date,
    create_user,
    create_date)
  VALUES(requestNum,
    letterTypeCode,
    aLegalCaseNum,
    holdDate,
    createUser,
    sysdate);

  END insertLetterRequest;
```

</details>

<details>
<summary>get_atty_mngr_email body</summary>

```sql
FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_email VARCHAR2(50);
   BEGIN
    BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = sch.asgn_attorney_emplid
           AND P.manager_emplid = emp.emplid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
         BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = 'OUTSIDECNCL'
           AND sch.asgn_other_firm_seq_num IS NOT NULL
           AND P.manager_emplid = emp.emplid;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_email := 'NONE';
     END;
  END;
RETURN v_email;

 END get_atty_mngr_email;
```

</details>

<details>
<summary>Header-related body excerpts</summary>

```sql
FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE AS
         v_hear_date DATE;
BEGIN
BEGIN
   SELECT MAX(sch.schedule_date)
     INTO v_hear_date
     FROM LEGAL_CASE cas,
          SCHEDULE sch,
          CONSOLIDATED_CASE c
   WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
    AND sch.schedule_seq_num =
             (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE sch2, CONSOLIDATED_CASE c2
             WHERE ((sch2.LEGAL_CASE_SEQ_NUM = cas.LEGAL_CASE_SEQ_NUM)
                OR (c2.secondary_case_seq_num = in_case_seq
               AND sch2.legal_case_seq_num = c2.primary_case_seq_num))
               AND sch2.SCHEDULE_TYPE_CODE = 'HEAR');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       NULL;
   END;

 RETURN v_hear_Date;
END get_hearing_date;

FUNCTION get_case_num(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_num VARCHAR2(15) := ' ';
   BEGIN
    BEGIN
    SELECT wcb_num INTO v_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NOT NULL
 UNION
    SELECT dcbs_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NOT NULL
 UNION
    SELECT owcp_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NOT NULL
 UNION
    SELECT oalj_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NOT NULL
 UNION
    SELECT brb_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NULL
       AND brb_num IS NOT NULL;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          v_num := ' ';
  END;
  RETURN v_num;

 END get_case_num;

FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
         RETURN VARCHAR2 AS
         v_atty VARCHAR2(50);
   BEGIN
    BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
      INTO v_atty
         FROM LEGAL_CASE cas,
               APPEAL app,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
            AND app.legal_case_seq_num = cas.legal_case_seq_num
            AND app.appeal_seq_num =
                (SELECT MAX(appeal_seq_num)
                   FROM APPEAL a2
                  WHERE a2.legal_case_Seq_num = cas.legal_case_seq_num)
             AND emp.emplid = app.atty_emplid;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
          INTO v_atty
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND emp.emplid = sch.asgn_attorney_emplid
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
      SELECT FIRM_NAME INTO v_atty
        FROM LEGAL_CASE cas,
               SCHEDULE sch,
               OTHER_FIRM frm
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND frm.other_firm_seq_num = sch.asgn_other_firm_Seq_num
            AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;

      EXCEPTION
          WHEN NO_DATA_FOUND THEN
               v_atty := '';
      END;
     END;
    END;
  RETURN v_atty;
END get_asgn_atty;

PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor)

   AS BEGIN OPEN recordset FOR
      SELECT LEGAL_CASE_SEQ_NUM LegalCaseSeqNum,
      CASE_TYPE_CODE CaseTypeCode,
      REQUEST_TYPE_CODE RequestTypeCode,
      WCB_NUM WcbNum,
      get_case_num(LegalCaseSeqNum) AS CASE_NUM,
      DCBS_NUM DcbsNum,
      CASE_STATUS_CODE CaseStatusCode,
      CLAIM_ID ClaimID,
      REQUEST_DATE RequestDate,
      WORKER_ATTY_FIRM_PROV_LOC_ID WorkerAttyFirmProvLocID,
      WORKER_ATTY_PROV_LOC_ID WorkerAttyProvLocID,
      OAR_DUE_DATE OarDueDate,
      OAR_MAILED_DATE OarMailedDate,
      LEGAL_FILE_CREATE_DATE LegalFileCreateDate,
      CASE_WEIGHT_VALUE CaseWeightValue,
      ACKNOWLEDGE_DATE AcknowledgeDate,
      REQUEST_NEW_WCB_NUM_FLAG RequestNewWcbNumFlag,
      REQUESTED_BY_CODE RequestedBYCode,
      REQUEST_RECEIPT_DATE RequestReceiptDate,
      REQUEST_POSTMARK_DATE RequestPostmarkDate,
      REQUEST_ALL_DAY_HEARING_FLAG RequestAllDayHearingFlag,
      STRESS_CLAIM_FLAG StressClaimFlag,
      RECON_PACKET_RECEIVED_DATE ReconPacketReceivedDate,
      DISMISSAL_DATE DismissalDate,
      SETTLEMENT_DATE SettlementDate,
      SEND_DEF_FLAG SendDefFlag,
      RECON_LETTER_MAILED_DATE ReconLetterMailedDate,
      DEF_SENT_DATE DefSentDate,
      EMPLOYER_EVALUATION_NUM EmployerEvaluationNum,
      CONSOLIDATE_CASE_FLAG ConsolidateCaseFlag,
      OWCP_NUM OwcpNum,
      OALJ_NUM OaljNum,
      BRB_NUM BrbNum,
      CREATE_DATE CreateDate,
      CREATE_USER CreateUser,
      UPDATE_DATE UpdateDate,
      UPDATE_USER UpdateUser,
      MOST_RECENT_HEARING_SEQ_NUM as MostRecentHearingSeqNum,
      CLAIM_IMAGE_ALERTS_FLAG ClaimImageAlertsFlag,
      getClaimImageAlertsUpdateDate(LegalCaseSeqNum) AS ClaimImageAlertsUpdateDate
      FROM LEGAL_CASE C
      WHERE (legal_case_seq_num = LegalCaseSeqNum  and ClaimId = 0)
         OR (LegalCaseSeqNum = 0 and Claim_id = ClaimId);
END getLegalCase;
```

</details>

## Calling Code References

| Class | Method | Source File |
| --- | --- | --- |
| `LegalManagerBase` | `insertLetterRequest` | `SAIF.Application.Claim.Legal\LegalManagerBase.vb` |
| `clsLegalData` | `New` / `RetrieveSQL` (`pk80001_legalNet.getHeader`) | `SAIF.Application.Claim.Legal\clsLegalData.vb` |
| `clsScheduleManager` | `getAttyMngrEmail` | `SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb` |

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources
- Oracle source retrieval: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk80001_legalnet.json`
- `SAIF.Application.Claim.Legal\LegalManagerBase.vb`
- `SAIF.Application.Claim.Legal\clsLegalData.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb`